package com.bit.frige.DB;

import java.util.ArrayList;
import java.util.List;

import com.bit.frige.recipe.RecipeListDTO;

import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;

public class DBContactHelper extends SQLiteOpenHelper {
	// Database Version
	private static final int DATABASE_VERSION = 12;
	// Database Name
	private static final String DATABASE_NAME = "frige";
	// Table Name
	private static final String TABLE_FOOD = "foodinfo";
	private static final String TABLE_USER = "userinfo";
	private static final String TABLE_INFO = "infodata";
	private static final String TABLE_RECIPE = "recipe"; //레시피 추가
	private static final String TABLE_BASKET = "basket"; 

	//Food Table Columns names
	private static final String KEY_ID = "_id";
	private static final String KEY_CATENO = "cateno";
	private static final String KEY_FOODNAME = "foodname";
	private static final String KEY_BUYDATE = "buydate";
	private static final String KEY_SELFLIFE = "selflife";
	private static final String KEY_LOCATE = "locate";
	private static final String KEY_INFO = "info";
	//RECIPE TABLE
	private static final String KEY_RECIPENO = "_id";     //레시피 추가
	private static final String KEY_RECIPENAME= "recipename";  //레시피 추가
	private static final String KEY_RECIPEURL = "recipeurl";   //레시피 추가
	//basket TABLE
	private static final String KEY_BASKETNO = "_id";     //장바구니 추가
	private static final String KEY_USERITEM= "useritem";  //장바구니 추가

	public DBContactHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
	}
	@Override
	public void onCreate(SQLiteDatabase db) {
		String CREATE_FOODINFO_TABLE = "CREATE TABLE " + TABLE_FOOD + "("
				+ KEY_ID + " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_CATENO
				+ " INTEGER," + KEY_FOODNAME + " TEXT," + KEY_SELFLIFE
				+ " INTEGER)";
		String CREATE_INFO_TABLE = "CREATE TABLE " + TABLE_INFO + "(" + KEY_ID
				+ " INTEGER PRIMARY KEY ," + KEY_INFO + " INTEGER)";
		String CREATE_USER_TABLE = "CREATE TABLE " + TABLE_USER + "(" + KEY_ID
				+ " INTEGER PRIMARY KEY AUTOINCREMENT," + KEY_CATENO
				+ " INTEGER," + KEY_FOODNAME + " TEXT," + KEY_BUYDATE
				+ " TEXT," + KEY_SELFLIFE + " TEXT," + KEY_LOCATE + " INTEGER)";
		String CREATE_RECIPE_TABLE = "CREATE TABLE " + TABLE_RECIPE + "(" + KEY_RECIPENO  //레시피 추가
				+ " INTEGER PRIMARY KEY ," + KEY_RECIPENAME + " TEXT, "                   //레시피 추가
				+  KEY_RECIPEURL + " TEXT)";                                              //레시피 추가
		
		String CREATE_TABLE_BASKET = "CREATE TABLE " + TABLE_BASKET + "(" + KEY_BASKETNO  //장바구니 추가
				+ " INTEGER PRIMARY KEY AUTOINCREMENT ," + KEY_USERITEM + " TEXT)";                                              //레시피 추가

		db.execSQL(CREATE_FOODINFO_TABLE);
		db.execSQL(CREATE_INFO_TABLE);
		db.execSQL(CREATE_USER_TABLE);
		db.execSQL(CREATE_RECIPE_TABLE);                       //레시피 추가
		db.execSQL(CREATE_TABLE_BASKET);                                 //장바구니 추가
		Log.d("dd", "recipe1");                                
	}
	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_FOOD);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_INFO);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_USER);
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_RECIPE);              //레시피 추가
		db.execSQL("DROP TABLE IF EXISTS " + TABLE_BASKET);              //장바구니 추가
		Log.d("dd", "recipe1"); 
		// Create tables again
		onCreate(db);
	}
	// 접속정보 추가
	public void addInfo() {
		SQLiteDatabase db = this.getWritableDatabase();
		ContentValues values = new ContentValues();
		values.put(KEY_ID, 1);
		values.put(KEY_INFO, 1);
		db.insert(TABLE_INFO, null, values);
		db.close(); 
	}
	// 접속정보 가져오기
	public int getInfo(int id) {
		SQLiteDatabase db = this.getReadableDatabase();
		List<FoodDTO> foodList = new ArrayList<FoodDTO>();
		Cursor cursor = db.query(TABLE_INFO, new String[] { KEY_ID, KEY_INFO },
				KEY_ID + "=?", new String[] { String.valueOf(id) }, null, null,
				null, null);
		int key = 0;
		if (cursor.moveToFirst()) {
			key = cursor.getInt(1);
		}
		return key;
	}

	// 새로운 식품정보 함수 추가
	public void addContact(FoodDTO food) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_CATENO, food.getCateno());
		values.put(KEY_FOODNAME, food.getFoodname());
		values.put(KEY_SELFLIFE, food.getSelflife());
	
		db.insert(TABLE_FOOD, null, values);
		db.close(); 
	}

	public ArrayList<FoodDTO> getFoodCategori(int id) {
		SQLiteDatabase db = this.getReadableDatabase();
		ArrayList<FoodDTO> foodList = new ArrayList<FoodDTO>();
		Cursor cursor = db.query(TABLE_FOOD, new String[] { KEY_ID, KEY_CATENO,
				KEY_FOODNAME, KEY_SELFLIFE }, KEY_CATENO + "=?",
				new String[] { String.valueOf(id) }, null, null, null, null);
		FoodDTO food;
		if (cursor.moveToFirst()) {
			do {
				food = new FoodDTO();
				food.setId(cursor.getInt(0));
				food.setCateno(cursor.getInt(1));
				food.setFoodname(cursor.getString(2));
				food.setSelflife(cursor.getInt(3));
				
				foodList.add(food);
			} while (cursor.moveToNext());
		}
		return foodList;
	}
	// 모든 식품정보  정보 가져오기
	public ArrayList<FoodDTO> getAllFoodInfo() {
		ArrayList<FoodDTO> foodList = new ArrayList<FoodDTO>();
		
		String selectQuery = "SELECT  * FROM " + TABLE_FOOD;

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		FoodDTO food = null;
		if (cursor.moveToFirst()) {
			do {
				food = new FoodDTO();
				food.setId(cursor.getInt(0));
				food.setCateno(cursor.getInt(1));
				food.setFoodname(cursor.getString(2));
				food.setSelflife(cursor.getInt(3));
				// Adding contact to list
				foodList.add(food);
			} while (cursor.moveToNext());
		}

		return foodList;
	}

	// 식품정보  갯수
	public int getContactsCount() {
		String countQuery = "SELECT  * FROM " + TABLE_FOOD;
		SQLiteDatabase db = this.getReadableDatabase();
		Cursor cursor = db.rawQuery(countQuery, null);
		int cnt = cursor.getCount();
		cursor.close();
		
		return cnt;
	}
	//사용자 정보 입력
	public void addUserInfo(FoodDTO food) {
		SQLiteDatabase db = this.getWritableDatabase();
		Log.d("DB?", "야" + food.getCateno());
		Log.d("DB?", "아야" + food.getFoodname());
		Log.d("DB?", "아야" + food.getBuydate());
		Log.d("DB?", "아야" + food.getSelfdate());
		Log.d("DB?", "아야" + food.getPlace());

		ContentValues values = new ContentValues();
		values.put(KEY_CATENO, food.getCateno());
		values.put(KEY_FOODNAME, food.getFoodname());
		values.put(KEY_BUYDATE, food.getBuydate());
		values.put(KEY_SELFLIFE, food.getSelfdate());
		values.put(KEY_LOCATE, food.getPlace());

	
		db.insert(TABLE_USER, null, values);
		db.close(); 
	}
	//모든 사용자정보 가져오기
	public ArrayList<FoodDTO> getAllUserInfo() {
		ArrayList<FoodDTO> foodList = new ArrayList<FoodDTO>();
		String selectQuery = "SELECT  * FROM " + TABLE_USER + " ORDER BY "
				+ KEY_SELFLIFE + " ASC; ";
		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.rawQuery(selectQuery, null);

		FoodDTO food = null;
		if (cursor.moveToFirst()) {
			do {
				food = new FoodDTO();
				food.setId(cursor.getInt(0));
				food.setCateno(cursor.getInt(1));
				food.setFoodname(cursor.getString(2));
				food.setBuydate(cursor.getString(3));
				food.setSelfdate(cursor.getString(4));
				food.setPlace(cursor.getInt(5));

				// Adding contact to list
				foodList.add(food);
			} while (cursor.moveToNext());
		}

		// return contact list
		return foodList;
	}
	//해당위치의 사용자정보 가져오기
	public ArrayList<FoodDTO> getAllUserInfo(int loacate) {
		ArrayList<FoodDTO> foodList = new ArrayList<FoodDTO>();

		SQLiteDatabase db = this.getWritableDatabase();
		Cursor cursor = db.query(TABLE_USER, new String[] { KEY_ID, KEY_CATENO,
				KEY_FOODNAME, KEY_BUYDATE, KEY_SELFLIFE, KEY_LOCATE },
				KEY_LOCATE + "=?", new String[] { String.valueOf(loacate) },
				null, null, null, null);

		FoodDTO food = null;
		if (cursor.moveToFirst()) {
			do {
				food = new FoodDTO();
				food.setId(cursor.getInt(0));
				food.setCateno(cursor.getInt(1));
				food.setFoodname(cursor.getString(2));
				food.setBuydate(cursor.getString(3));
				food.setSelfdate(cursor.getString(4));
				food.setPlace(cursor.getInt(5));

				// Adding contact to list
				foodList.add(food);
			} while (cursor.moveToNext());
		}

		// return contact list
		return foodList;
	}
	//해당 id 사용자정보 삭제
	public void deleteUserInfo(int id) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_USER, KEY_ID + " = ?",
				new String[] { String.valueOf(id) });
		Log.d("DB delete", "삭제 완료" + id);
		db.close();
	}
	//해당 id 사용자정보 가져오기
	public FoodDTO getUserInfo(int id) {
		SQLiteDatabase db = this.getReadableDatabase();
		List<FoodDTO> foodList = new ArrayList<FoodDTO>();
		Cursor cursor = db.query(TABLE_USER, new String[] { KEY_ID, KEY_CATENO,
				KEY_FOODNAME, KEY_BUYDATE, KEY_SELFLIFE, KEY_LOCATE },
				KEY_ID + "=?", new String[] { String.valueOf(id) },
				null, null, null, null);
		FoodDTO food = null;
		if (cursor.moveToFirst()) {
			food = new FoodDTO();
			food.setId(cursor.getInt(0));
			food.setCateno(cursor.getInt(1));
			food.setFoodname(cursor.getString(2));
			food.setBuydate(cursor.getString(3));
			food.setSelfdate(cursor.getString(4));
			food.setPlace(cursor.getInt(5));
		}
		return food;
	}
	//사용자정보 수정
	 public int updateUserInfo(FoodDTO food) {
	 SQLiteDatabase db = this.getWritableDatabase();
	
	 ContentValues values = new ContentValues();
		values.put(KEY_CATENO, food.getCateno());
		values.put(KEY_FOODNAME, food.getFoodname());
		values.put(KEY_BUYDATE, food.getBuydate());
		values.put(KEY_SELFLIFE, food.getSelfdate());
		values.put(KEY_LOCATE, food.getPlace());
	
	 return db.update(TABLE_USER, values, KEY_ID + " = ?",
	 new String[] { String.valueOf(food.getId()) });
	 }
	// 엑셀의 레시피 리스트 데이터 추가 
	public void addRecipe(RecipeListDTO recipe) {
		SQLiteDatabase db = this.getWritableDatabase();

		ContentValues values = new ContentValues();
		values.put(KEY_RECIPENO, recipe.getRecipeNo());
		values.put(KEY_RECIPENAME, recipe.getFoodname());
		values.put(KEY_RECIPEURL, recipe.getRecipeUrl());
		db.insert(TABLE_RECIPE, null, values);
		Log.d("dd", "recipe4");
		db.close(); 
	}
	//레시피 목록 가져오기
	public ArrayList<RecipeListDTO> getRecipe(){
		ArrayList<RecipeListDTO> List = new ArrayList<RecipeListDTO>();
		SQLiteDatabase db = this.getWritableDatabase();

		String selectQuery = "SELECT  * FROM " + TABLE_RECIPE; 
		Cursor cursor = db.rawQuery(selectQuery, null);

		RecipeListDTO recipe = null;
		if (cursor.moveToFirst()) {
			do {
				recipe = new RecipeListDTO();
				recipe.setRecipeNo(cursor.getInt(0));
				recipe.setFoodname(cursor.getString(1));
				recipe.setRecipeUrl(cursor.getString(2));
				
				List.add(recipe);
			} while (cursor.moveToNext());
		}
		return List;
	}
	
	//장바구니 항목 읽어오기
	public ArrayList<BasketDTO> getBasket(){
		ArrayList<BasketDTO> List = new ArrayList<BasketDTO>();
		SQLiteDatabase db = this.getWritableDatabase();

		String selectQuery = "SELECT  * FROM " + TABLE_BASKET; 
		Cursor cursor = db.rawQuery(selectQuery, null);

		BasketDTO basket = null;
		if (cursor.moveToFirst()) {
			do {
				basket = new BasketDTO();
				basket.setBasketNo(cursor.getInt(0));
				basket.setName(cursor.getString(1));	
				List.add(basket);
			} while (cursor.moveToNext());
		}
		return List;
	}
	
	// 장바구니 항목 추가
	public void addBasket(String Item) {
		SQLiteDatabase db = this.getWritableDatabase();
		
		ContentValues values = new ContentValues();
		//values.put(KEY_CATENO, basket.getBasketNo());
		values.put(KEY_USERITEM,Item);

		db.insert(TABLE_BASKET, null, values);
		db.close(); // Closing database connection
	}
	
	//장바구니 전체 항목 삭제
	public void deleteBasket(String name) {
		SQLiteDatabase db = this.getWritableDatabase();
		db.delete(TABLE_BASKET, KEY_USERITEM + " = ?",
				new String[] { String.valueOf(name) });
		Log.d("DB delete  " + TABLE_BASKET, "삭제 완료" + name);
		db.close();
	}
	
}
